﻿using Com.JunCaiSoft.Common.Dao;
using Com.JunCaiSoft.ConstructionCashFlow.Common.Enum;
using Com.JunCaiSoft.ConstructionCashFlow.Entity;
using Com.JunCaiSoft.ConstructionCashFlow.IDao;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Com.JunCaiSoft.ConstructionCashFlow.Dao
{
    public class BankSettlementDao : CoreDALAsync<BankSettlement>, IBankSettlementDao
    {
        public BankSettlementDao(DbContext context) : base(context)
        {

        }

        public DbContext dbContext { get { return baseContext; } }
		/// <summary>
		/// 银行预结算
		/// </summary>
		/// <param name="BeginDate"></param>
		/// <param name="EndDate"></param>
		/// <param name="ProcessingType"></param>
		/// <param name="Period"></param>
		/// <returns></returns>
        public async Task<bool> SettlementBankAsync(DateTime BeginDate, DateTime EndDate,Int32 ProcessingType, SettlementPeriod Period)
        {
            try
            {
                var sql = @"INSERT INTO BankSettlement  
							(
								Id,Num,NumCode,SettlementBankId,BeginTime,EndTime,SettlementPeriod,
								BeginBalance,EndBalance,BeginCommercialTicket,EndCommercialTicket,
								BeginFreezeMoney,EndFreezeMoney,BeginFlowBalance,EndFlowBalance,
								IsSettlement,CreateTime
							)
                            SELECT
								NEWID() Id,
								1 Num,
								1607257771 NumCode,
								a.Id SettlementBankId,
								CAST ( @BeginTime AS datetime2 ( 7 ) ) BeginTime,
								CAST ( @EndTime AS datetime2 ( 7 ) ) EndTime,
								@Period SettlementPeriod,
								ISNULL(a.BeginBalance,0.0) BeginBalance,
								ISNULL(ISNULL(a.BeginBalance,0.0) + ISNULL(a.Income,0.0) - ISNULL(a.Cost,0.0),0.0) EndBalance,
								ISNULL(a.BeginCommercialTicket,0.0) BeginCommercialTicket,
								ISNULL(ISNULL(a.BeginCommercialTicket,0.0) + ISNULL(a.IncomeCommercialTicket,0.0) - ISNULL(a.CostCommercialTicket,0.0),0.0) EndCommercialTicket,
								ISNULL(a.BeginFreezeMoney,0.0) BeginFreezeMoney,
								ISNULL(a.EndFreezeMoney,0.0) EndFreezeMoney,
								ISNULL(ISNULL(a.BeginBalance,0.0) - ISNULL(a.BeginFreezeMoney,0.0),0.0) BeginFlowBalance,
								ISNULL(ISNULL(a.BeginBalance,0.0) + ISNULL(a.Income,0.0) - ISNULL(a.Cost,0.0) - ISNULL(a.EndFreezeMoney,0.0),0.0) EndFlowBalance,
								0 IsSettlement,
								GETDATE() CreateTime 
							FROM
								(
									SELECT
										a.Id,
										ROUND( AVG ( b.EndBalance ), 2 ) BeginBalance,
										ROUND( AVG ( b.EndCommercialTicket ), 2 ) BeginCommercialTicket,
										ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '商票' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeCommercialTicket,
										ROUND( SUM ( CASE WHEN c.BankForward = 0 THEN c.Amount ELSE 0.0 END ), 2 ) Income,
										ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '商票' THEN c.Amount ELSE 0.0 END ), 2 ) CostCommercialTicket,
										ROUND( SUM ( CASE WHEN c.BankForward = 1 THEN c.Amount ELSE 0.0 END ), 2 ) Cost,
										ROUND( AVG ( b.BeginFreezeMoney ), 2 ) BeginFreezeMoney,
										ROUND( AVG ( b.EndFreezeMoney ), 2 ) EndFreezeMoney 
									FROM
										Bank a
										LEFT JOIN 
										(
											SELECT
												a.SettlementBankId,
												ISNULL(ROUND( ISNULL(a.BeginBalance,0.0) + ISNULL(Income,0.0) - ISNULL(Cost,0.0), 2 ),0.0 )EndBalance ,
												ISNULL(ROUND( ISNULL(a.BeginCommercialTicket,0.0) + ISNULL(IncomeCommercialTicket,0.0) - ISNULL(CostCommercialTicket,0.0), 2 ),0.0 )EndCommercialTicket ,
												a.BeginFreezeMoney,
												a.EndFreezeMoney
											FROM
												BankSettlement a
												LEFT JOIN 
												(
													SELECT
														a.BankId,
														ROUND( SUM ( CASE WHEN a.BankForward = 0 AND b.Name = '商票' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeCommercialTicket,
														ROUND( SUM ( CASE WHEN a.BankForward = 0 THEN a.Amount ELSE 0.0 END ), 2 ) Income,
														ROUND( SUM ( CASE WHEN a.BankForward = 1 AND b.Name = '商票' THEN a.Amount ELSE 0.0 END ), 2 ) CostCommercialTicket,
														ROUND( SUM ( CASE WHEN a.BankForward = 1 THEN a.Amount ELSE 0.0 END ), 2 ) Cost 
													FROM
														CashFlow a LEFT JOIN FundsType b ON a.FundsType = b.Code
													WHERE
														a.AllocationTime < @BeginTime 
													GROUP BY
														a.BankId 
												) b ON a.SettlementBankId = b.BankId 
											WHERE
												a.SettlementPeriod = 0 
										) b ON a.Id = b.SettlementBankId
									LEFT JOIN ( SELECT * FROM CashFlow WHERE AllocationTime >= @BeginTime AND AllocationTime <= @EndTime ) c ON a.Id = c.BankId 
									LEFT JOIN FundsType d ON c.FundsType = d.Code
									GROUP BY
										a.Id 
							) a";

                List<BankSettlement> bankSettlementList = (await ListAsync(x => x.BeginTime == BeginDate && x.EndTime == EndDate && x.SettlementPeriod == Period));

                if(bankSettlementList.Count != 0 && ProcessingType == 1)
                {
					await DeleteListAsync(bankSettlementList);
				}

                if (bankSettlementList.Count == 0 || ProcessingType == 1)
                {
					List<SqlParameter> settlementParams = new List<SqlParameter>();

					SqlParameter BeginTime = new SqlParameter("@BeginTime", BeginDate.ToString("yyyyMMdd"));

					SqlParameter EndTime = new SqlParameter("@EndTime", EndDate.ToString("yyyyMMdd"));

					SqlParameter computePeriod = new SqlParameter("@Period", Period);

					settlementParams.Add(BeginTime); settlementParams.Add(EndTime); settlementParams.Add(computePeriod);

					dbContext.Database.ExecuteSqlRaw(sql, settlementParams.Select(x => ((ICloneable)x).Clone()).ToArray());
				}

				return true;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}
